--本Schema未使用，备用
--ACL_SID->
call DROP_TABLE_IFEXISTS('ACL_SID');
call DROP_SEQUENCE_IFEXISTS('ACSI_SEQ');

CREATE TABLE ACL_SID (
	ID NUMBER(19) NOT NULL,
	PRINCIPAL NUMBER(1) NOT NULL,
	SID VARCHAR2(100 CHAR) NOT NULL,
	CONSTRAINT ACSI_PK PRIMARY KEY (ID),
	CONSTRAINT ACSI_UK UNIQUE(SID,PRINCIPAL)
);
ALTER TABLE ACL_SID ADD CONSTRAINT PRINCIPAL_BOOLEAN_CHK CHECK(PRINCIPAL in (1,0));
CREATE SEQUENCE ACSI_SEQ
	INCREMENT BY 1
	START WITH 100
	MINVALUE 1
	NOCACHE
	NOORDER;
--<-

--ACL_CLASS->
call DROP_TABLE_IFEXISTS('ACL_CLASS');
call DROP_SEQUENCE_IFEXISTS('ACCL_SEQ');
CREATE TABLE ACL_CLASS (
	ID NUMBER(19) NOT NULL,
	CLASS VARCHAR2(100 CHAR) NOT NULL,
	CONSTRAINT ACCL_PK PRIMARY KEY (ID),
	CONSTRAINT ACCL_UK UNIQUE(CLASS)
);
CREATE SEQUENCE ACCL_SEQ
	INCREMENT BY 1
	START WITH 100
	MINVALUE 1
	NOCACHE
	NOORDER;
--<-

--ACL_OBJECT_IDENTITY->
call DROP_TABLE_IFEXISTS('ACL_OBJECT_IDENTITY');
call DROP_SEQUENCE_IFEXISTS('ACOI_SEQ');
CREATE TABLE ACL_OBJECT_IDENTITY (
	ID NUMBER(19) NOT NULL,
	OBJECT_ID_CLASS NUMBER(19) NOT NULL,
	OBJECT_ID_IDENTITY NUMBER(19) NOT NULL,
	PARENT_OBJECT NUMBER(19),
	OWNER_SID NUMBER(19),
	ENTRIES_INHERITING NUMBER(1) NOT NULL,
	CONSTRAINT ACOI_PK PRIMARY KEY (ID),
	CONSTRAINT ACOI_UK UNIQUE(OBJECT_ID_CLASS,OBJECT_ID_IDENTITY)
);
ALTER TABLE ACL_OBJECT_IDENTITY ADD CONSTRAINT INHERITING_BOOLEAN_CHK CHECK(ENTRIES_INHERITING in (1,0));
ALTER TABLE ACL_OBJECT_IDENTITY ADD CONSTRAINT ACOI_ACOI_FK FOREIGN KEY (PARENT_OBJECT) REFERENCES ACL_OBJECT_IDENTITY(ID) ON DELETE CASCADE;
ALTER TABLE ACL_OBJECT_IDENTITY ADD CONSTRAINT ACOI_ACCL_FK FOREIGN KEY (OBJECT_ID_CLASS) REFERENCES ACL_CLASS(ID) ON DELETE CASCADE;
ALTER TABLE ACL_OBJECT_IDENTITY ADD CONSTRAINT ACOI_ACSI_FK FOREIGN KEY (OWNER_SID) REFERENCES ACL_SID(ID) ON DELETE CASCADE;
CREATE SEQUENCE ACOI_SEQ
	INCREMENT BY 1
	START WITH 100
	MINVALUE 1
	NOCACHE
	NOORDER;
--<-

--ACL_ENTRY->
call DROP_TABLE_IFEXISTS('ACL_ENTRY');
call DROP_SEQUENCE_IFEXISTS('ACEN_SEQ');
CREATE TABLE ACL_ENTRY (
	ID NUMBER(19) NOT NULL,
	ACL_OBJECT_IDENTITY NUMBER(19) NOT NULL,
	ACE_ORDER NUMBER(10) NOT NULL,
	SID NUMBER(19) NOT NULL,
	MASK NUMBER(10) NOT NULL,
	GRANTING NUMBER(1) NOT NULL,	
	AUDIT_SUCCESS NUMBER(1) NOT NULL,
	AUDIT_FAILURE NUMBER(1) NOT NULL,	
	CONSTRAINT ACEN_PK PRIMARY KEY (ID),
	CONSTRAINT ACEN_UK UNIQUE(ACL_OBJECT_IDENTITY,ACE_ORDER)
);
ALTER TABLE ACL_ENTRY ADD CONSTRAINT GRANTING_BOOLEAN_CHK CHECK(GRANTING in (1,0));
ALTER TABLE ACL_ENTRY ADD CONSTRAINT AUDIT_SUCCESS_BOOLEAN_CHK CHECK(AUDIT_SUCCESS in (1,0));
ALTER TABLE ACL_ENTRY ADD CONSTRAINT AUDIT_FAILURE_BOOLEAN_CHK CHECK(AUDIT_FAILURE in (1,0));
ALTER TABLE ACL_ENTRY ADD CONSTRAINT ACEN_ACOI_FK FOREIGN KEY (ACL_OBJECT_IDENTITY) REFERENCES ACL_OBJECT_IDENTITY(ID) ON DELETE CASCADE;
ALTER TABLE ACL_ENTRY ADD CONSTRAINT ACEN_ACSI_FK FOREIGN KEY (SID) REFERENCES ACL_SID(ID) ON DELETE CASCADE;
CREATE SEQUENCE ACEN_SEQ
	INCREMENT BY 1
	START WITH 100
	MINVALUE 1
	NOCACHE
	NOORDER;
--<-

--TRIGGER FOR EACH TABLE->
CREATE OR REPLACE TRIGGER ACL_SID_TRG BEFORE INSERT ON ACL_SID FOR EACH ROW
	DECLARE 
		intergrity_error exception;
		errno integer;
		errmsg char(200);
		dummy integer;
		found boolean;
	BEGIN
		SELECT ACSI_SEQ.NEXTVAL INTO :new.ID from dual;
		--Error handling
		EXCEPTION
			WHEN intergrity_error THEN RAISE_APPLICATION_ERROR(errno, errmsg);
	END;
/
CREATE OR REPLACE TRIGGER ACL_OBJECT_IDENTITY_TRG BEFORE INSERT ON ACL_OBJECT_IDENTITY FOR EACH ROW
	DECLARE 
		intergrity_error exception;
		errno integer;
		errmsg char(200);
		dummy integer;
		found boolean;
	BEGIN
		SELECT ACOI_SEQ.NEXTVAL INTO :new.ID from dual;
		--Error handling
		EXCEPTION
			WHEN intergrity_error THEN RAISE_APPLICATION_ERROR(errno, errmsg);
	END;
/
CREATE OR REPLACE TRIGGER ACL_ENTRY_TRG BEFORE INSERT ON ACL_ENTRY FOR EACH ROW
	DECLARE 
		intergrity_error exception;
		errno integer;
		errmsg char(200);
		dummy integer;
		found boolean;
	BEGIN
		SELECT ACEN_SEQ.NEXTVAL INTO :new.ID from dual;
		--Error handling
		EXCEPTION
			WHEN intergrity_error THEN RAISE_APPLICATION_ERROR(errno, errmsg);
	END;
/
CREATE OR REPLACE TRIGGER ACL_CLASS_TRG BEFORE INSERT ON ACL_CLASS FOR EACH ROW
	DECLARE 
		intergrity_error exception;
		errno integer;
		errmsg char(200);
		dummy integer;
		found boolean;
	BEGIN
		SELECT ACCL_SEQ.NEXTVAL INTO :new.ID from dual;
		--Error handling
		EXCEPTION
			WHEN intergrity_error THEN RAISE_APPLICATION_ERROR(errno, errmsg);
	END;
/
--<-

